using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Data.SqlClient;
using Entities;
/// <summary>
/// Summary description for PhieuGhiDanhDAL
/// </summary>
namespace DataAccess
{

    public class PhieuGhiDanhDAL : BaseDAL
    {
        #region Private Variables

        #endregion

        #region Public Constructors
        public PhieuGhiDanhDAL()
        {
            //
            // TODO: Add constructor logic here
            //

        }
        #endregion



        #region Public Methods

        public int Insert(PhieuGhiDanh objPhieuGhiDanh)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "PhieuGhiDanh_Insert";
            SqlParameter Sqlparam;

            Sqlparam = new SqlParameter("@KhoaHoc", SqlDbType.BigInt);
            Sqlparam.Value = objPhieuGhiDanh.KhoaHoc;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DoiTuongGhiDanh", SqlDbType.BigInt);
            Sqlparam.Value = objPhieuGhiDanh.DoiTuongGhiDanh;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@HangXe", SqlDbType.BigInt);
            Sqlparam.Value = objPhieuGhiDanh.HangXe;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DaNopGiayKhamSucKhoe", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.DaNopGiayKhamSucKhoe;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DaNopCMND", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.DaNopCMND;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DaNopDuAnh", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.DaNopDuAnh;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@HocNgoaiGio", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.HocNgoaiGio;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@SoTienHocChinhThuc", SqlDbType.Decimal);
            Sqlparam.Value = objPhieuGhiDanh.SoTienHocChinhThuc;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@SoTienHocNgoaiGio", SqlDbType.Decimal);
            Sqlparam.Value = objPhieuGhiDanh.SoTienHocNgoaiGio;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@TongSoTienHoc", SqlDbType.Decimal);
            Sqlparam.Value = objPhieuGhiDanh.TongSoTienHoc;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@TrangThaiPhieuGhiDanh", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.TrangThaiPhieuGhiDanh;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@ThayGiao", SqlDbType.BigInt);
            Sqlparam.Value = objPhieuGhiDanh.ThayGiao;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@TrangThaiGuiMailDuyet", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.TrangThaiGuiMailDuyet;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@TrangThaiGuiMailDangKy", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.TrangThaiGuiMailDangKy;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@TrangThaiGuiMailDaThuTien", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.TrangThaiGuiMailDaThuTien;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@ID", SqlDbType.Int);
            Sqlparam.Direction = ParameterDirection.ReturnValue;
            Sqlcomm.Parameters.Add(Sqlparam);


            int result = base.ExecuteNoneQuery(Sqlcomm);

            if (!Convert.IsDBNull(Sqlcomm.Parameters["@ID"]))
                result = Convert.ToInt32(Sqlcomm.Parameters["@ID"].Value);

            return result;
        }

        public int Update(PhieuGhiDanh objPhieuGhiDanh)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "PhieuGhiDanh_UpdateByPK";
            SqlParameter Sqlparam;

            Sqlparam = new SqlParameter("@Khoa", SqlDbType.BigInt);
            Sqlparam.Value = objPhieuGhiDanh.Khoa;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@KhoaHoc", SqlDbType.BigInt);
            Sqlparam.Value = objPhieuGhiDanh.KhoaHoc;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DoiTuongGhiDanh", SqlDbType.BigInt);
            Sqlparam.Value = objPhieuGhiDanh.DoiTuongGhiDanh;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@HangXe", SqlDbType.BigInt);
            Sqlparam.Value = objPhieuGhiDanh.HangXe;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DaNopGiayKhamSucKhoe", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.DaNopGiayKhamSucKhoe;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DaNopCMND", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.DaNopCMND;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DaNopDuAnh", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.DaNopDuAnh;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@HocNgoaiGio", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.HocNgoaiGio;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@SoTienHocChinhThuc", SqlDbType.Decimal);
            Sqlparam.Value = objPhieuGhiDanh.SoTienHocChinhThuc;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@SoTienHocNgoaiGio", SqlDbType.Decimal);
            Sqlparam.Value = objPhieuGhiDanh.SoTienHocNgoaiGio;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@TongSoTienHoc", SqlDbType.Decimal);
            Sqlparam.Value = objPhieuGhiDanh.TongSoTienHoc;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@TrangThaiPhieuGhiDanh", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.TrangThaiPhieuGhiDanh;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@ThayGiao", SqlDbType.BigInt);
            Sqlparam.Value = objPhieuGhiDanh.ThayGiao;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@TrangThaiGuiMailDuyet", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.TrangThaiGuiMailDuyet;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@TrangThaiGuiMailDangKy", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.TrangThaiGuiMailDangKy;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@TrangThaiGuiMailDaThuTien", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.TrangThaiGuiMailDaThuTien;
            Sqlcomm.Parameters.Add(Sqlparam);



            Sqlparam = new SqlParameter("@ErrorCode", SqlDbType.Int);
            Sqlparam.Direction = ParameterDirection.ReturnValue;
            Sqlcomm.Parameters.Add(Sqlparam);

            int result = base.ExecuteNoneQuery(Sqlcomm);

            if (!Convert.IsDBNull(Sqlcomm.Parameters["@ErrorCode"]))
                result = Convert.ToInt32(Sqlcomm.Parameters["@ErrorCode"].Value);

            return result;


        }

        public int Delete(PhieuGhiDanh objPhieuGhiDanh)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "PhieuGhiDanh_DeleteByPK";
            SqlParameter Sqlparam;


            Sqlparam = new SqlParameter("@TrangThaiGuiMailDaThuTien", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.TrangThaiGuiMailDaThuTien;
            Sqlcomm.Parameters.Add(Sqlparam);



            int result = base.ExecuteNoneQuery(Sqlcomm);
            return result;
        }

        public int DeleteAll()
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "PhieuGhiDanh_DeleteAll";

            int result = base.ExecuteNoneQuery(Sqlcomm);
            return result;
        }

        public PhieuGhiDanh Select(PhieuGhiDanh objPhieuGhiDanh)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "PhieuGhiDanh_GetByPK";
            SqlParameter Sqlparam;


            Sqlparam = new SqlParameter("@TrangThaiGuiMailDaThuTien", SqlDbType.Int);
            Sqlparam.Value = objPhieuGhiDanh.TrangThaiGuiMailDaThuTien;
            Sqlcomm.Parameters.Add(Sqlparam);



            DataSet ds = base.GetDataSet(Sqlcomm);
            DataRow dr = null;
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                dr = ds.Tables[0].Rows[0];
                if (!Convert.IsDBNull(dr["Khoa"]))
                    objPhieuGhiDanh.Khoa = Convert.ToInt64(dr["Khoa"]);
                if (!Convert.IsDBNull(dr["KhoaHoc"]))
                    objPhieuGhiDanh.KhoaHoc = Convert.ToInt64(dr["KhoaHoc"]);
                if (!Convert.IsDBNull(dr["DoiTuongGhiDanh"]))
                    objPhieuGhiDanh.DoiTuongGhiDanh = Convert.ToInt64(dr["DoiTuongGhiDanh"]);
                if (!Convert.IsDBNull(dr["HangXe"]))
                    objPhieuGhiDanh.HangXe = Convert.ToInt64(dr["HangXe"]);
                if (!Convert.IsDBNull(dr["DaNopGiayKhamSucKhoe"]))
                    objPhieuGhiDanh.DaNopGiayKhamSucKhoe = Convert.ToInt32(dr["DaNopGiayKhamSucKhoe"]);
                if (!Convert.IsDBNull(dr["DaNopCMND"]))
                    objPhieuGhiDanh.DaNopCMND = Convert.ToInt32(dr["DaNopCMND"]);
                if (!Convert.IsDBNull(dr["DaNopDuAnh"]))
                    objPhieuGhiDanh.DaNopDuAnh = Convert.ToInt32(dr["DaNopDuAnh"]);
                if (!Convert.IsDBNull(dr["HocNgoaiGio"]))
                    objPhieuGhiDanh.HocNgoaiGio = Convert.ToInt32(dr["HocNgoaiGio"]);
                if (!Convert.IsDBNull(dr["SoTienHocChinhThuc"]))
                    objPhieuGhiDanh.SoTienHocChinhThuc = Convert.ToDecimal(dr["SoTienHocChinhThuc"]);
                if (!Convert.IsDBNull(dr["SoTienHocNgoaiGio"]))
                    objPhieuGhiDanh.SoTienHocNgoaiGio = Convert.ToDecimal(dr["SoTienHocNgoaiGio"]);
                if (!Convert.IsDBNull(dr["TongSoTienHoc"]))
                    objPhieuGhiDanh.TongSoTienHoc = Convert.ToDecimal(dr["TongSoTienHoc"]);
                if (!Convert.IsDBNull(dr["TrangThaiPhieuGhiDanh"]))
                    objPhieuGhiDanh.TrangThaiPhieuGhiDanh = Convert.ToInt32(dr["TrangThaiPhieuGhiDanh"]);
                if (!Convert.IsDBNull(dr["ThayGiao"]))
                    objPhieuGhiDanh.ThayGiao = Convert.ToInt64(dr["ThayGiao"]);
                if (!Convert.IsDBNull(dr["TrangThaiGuiMailDuyet"]))
                    objPhieuGhiDanh.TrangThaiGuiMailDuyet = Convert.ToInt32(dr["TrangThaiGuiMailDuyet"]);
                if (!Convert.IsDBNull(dr["TrangThaiGuiMailDangKy"]))
                    objPhieuGhiDanh.TrangThaiGuiMailDangKy = Convert.ToInt32(dr["TrangThaiGuiMailDangKy"]);
                if (!Convert.IsDBNull(dr["TrangThaiGuiMailDaThuTien"]))
                    objPhieuGhiDanh.TrangThaiGuiMailDaThuTien = Convert.ToInt32(dr["TrangThaiGuiMailDaThuTien"]);

            }
            return objPhieuGhiDanh;
        }

        public ArrayList SelectAllToArray()
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "PhieuGhiDanh_GetAll";

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = null;
            ArrayList arrPhieuGhiDanh = new ArrayList();
            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    PhieuGhiDanh objPhieuGhiDanh = new PhieuGhiDanh();
                    if (!Convert.IsDBNull(dr["Khoa"]))
                        objPhieuGhiDanh.Khoa = Convert.ToInt64(dr["Khoa"]);
                    if (!Convert.IsDBNull(dr["KhoaHoc"]))
                        objPhieuGhiDanh.KhoaHoc = Convert.ToInt64(dr["KhoaHoc"]);
                    if (!Convert.IsDBNull(dr["DoiTuongGhiDanh"]))
                        objPhieuGhiDanh.DoiTuongGhiDanh = Convert.ToInt64(dr["DoiTuongGhiDanh"]);
                    if (!Convert.IsDBNull(dr["HangXe"]))
                        objPhieuGhiDanh.HangXe = Convert.ToInt64(dr["HangXe"]);
                    if (!Convert.IsDBNull(dr["DaNopGiayKhamSucKhoe"]))
                        objPhieuGhiDanh.DaNopGiayKhamSucKhoe = Convert.ToInt32(dr["DaNopGiayKhamSucKhoe"]);
                    if (!Convert.IsDBNull(dr["DaNopCMND"]))
                        objPhieuGhiDanh.DaNopCMND = Convert.ToInt32(dr["DaNopCMND"]);
                    if (!Convert.IsDBNull(dr["DaNopDuAnh"]))
                        objPhieuGhiDanh.DaNopDuAnh = Convert.ToInt32(dr["DaNopDuAnh"]);
                    if (!Convert.IsDBNull(dr["HocNgoaiGio"]))
                        objPhieuGhiDanh.HocNgoaiGio = Convert.ToInt32(dr["HocNgoaiGio"]);
                    if (!Convert.IsDBNull(dr["SoTienHocChinhThuc"]))
                        objPhieuGhiDanh.SoTienHocChinhThuc = Convert.ToDecimal(dr["SoTienHocChinhThuc"]);
                    if (!Convert.IsDBNull(dr["SoTienHocNgoaiGio"]))
                        objPhieuGhiDanh.SoTienHocNgoaiGio = Convert.ToDecimal(dr["SoTienHocNgoaiGio"]);
                    if (!Convert.IsDBNull(dr["TongSoTienHoc"]))
                        objPhieuGhiDanh.TongSoTienHoc = Convert.ToDecimal(dr["TongSoTienHoc"]);
                    if (!Convert.IsDBNull(dr["TrangThaiPhieuGhiDanh"]))
                        objPhieuGhiDanh.TrangThaiPhieuGhiDanh = Convert.ToInt32(dr["TrangThaiPhieuGhiDanh"]);
                    if (!Convert.IsDBNull(dr["ThayGiao"]))
                        objPhieuGhiDanh.ThayGiao = Convert.ToInt64(dr["ThayGiao"]);
                    if (!Convert.IsDBNull(dr["TrangThaiGuiMailDuyet"]))
                        objPhieuGhiDanh.TrangThaiGuiMailDuyet = Convert.ToInt32(dr["TrangThaiGuiMailDuyet"]);
                    if (!Convert.IsDBNull(dr["TrangThaiGuiMailDangKy"]))
                        objPhieuGhiDanh.TrangThaiGuiMailDangKy = Convert.ToInt32(dr["TrangThaiGuiMailDangKy"]);
                    if (!Convert.IsDBNull(dr["TrangThaiGuiMailDaThuTien"]))
                        objPhieuGhiDanh.TrangThaiGuiMailDaThuTien = Convert.ToInt32(dr["TrangThaiGuiMailDaThuTien"]);
                    arrPhieuGhiDanh.Add(objPhieuGhiDanh);
                }
            }
            return arrPhieuGhiDanh;
        }

        public DataTable SelectAll()
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "PhieuGhiDanh_GetAll";

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = null;

            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];

            }
            return dt;
        }

        public DataTable SelectTrangThaiDangKy()
        {
            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "sp_PhieuGhiDanh_GetTinhTrangDangKy";

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = null;

            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];

            }
           
            return dt;
        }


        #endregion

    }

}
